package org.foreverframework.office;

import java.io.File;
import java.util.Date;

import jxl.Cell;
import jxl.CellView;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class MSExcelUtil {
	WritableWorkbook workbook;

	public MSExcelUtil() {

	}
	
	public MSExcelUtil(WritableWorkbook workbook){
		this.workbook = workbook;
	}

	public void createExcel(String filePath) throws Exception {
		workbook = Workbook.createWorkbook(new File(filePath));
	}

	public void createSheet(String sheetName, int index) throws Exception {
		workbook.createSheet(sheetName, index);
	}

	public void setCellLabel(int index, int c, int r, String cont)
			throws Exception {
		WritableSheet sheet = workbook.getSheet(index);
		Label label = new Label(c, r, cont);
		sheet.addCell(label);
	}

	public void setCellNumber(int index, int c, int r, double val)
			throws Exception {
		WritableSheet sheet = workbook.getSheet(index);
		Number number = new Number(c, r, val);
		sheet.addCell(number);
	}

	public void setCellColour(int index, int c, int r, Colour colour)
			throws Exception {
		WritableSheet sheet = workbook.getSheet(index);
		WritableFont writableFont = new WritableFont(WritableFont.ARIAL);
		writableFont.setColour(colour);
		WritableCellFormat writableCellFormat = new WritableCellFormat(
				writableFont);
		Cell cell = sheet.getCell(c, r);
		if (cell instanceof Label) {
			Label label = (Label) cell;
			label.setCellFormat(writableCellFormat);
		}
	}

	public void setCellImage(int index, int c, int r, double width,
			double height, String imagePath) throws Exception {
		WritableSheet sheet = workbook.getSheet(index);
		WritableImage img = new WritableImage(c, r, width, height, new File(
				imagePath));
		sheet.addImage(img);
	}

	public void setColumnWidth(int index, int c, int width) throws Exception {
		WritableSheet sheet = workbook.getSheet(index);
		sheet.setColumnView(c, width);
	}

	public void setCellFormula(int index, int c, int r, String formula)
			throws Exception {
		WritableSheet sheet = workbook.getSheet(index);
		Formula f = new Formula(2, 6, formula);
		sheet.addCell(f);
	}

	public void setCellDate(int index, int c, int r, Date date)
			throws Exception {
		WritableSheet sheet = workbook.getSheet(index);
		DateTime dateTime = new DateTime(c, r, date);
		sheet.addCell(dateTime);
	}

	public void setCellDate(int index, int c, int r, Date date, String format)
			throws Exception {
		WritableSheet sheet = workbook.getSheet(index);
		DateFormat dtf = new DateFormat(format);
		WritableCellFormat writableCellFormat = new WritableCellFormat(dtf);
		DateTime dateTime = new DateTime(c, r, date, writableCellFormat);
		sheet.addCell(dateTime);
	}

	public void setCellBorderColor(int index, int c, int r) throws Exception {
		WritableSheet sheet = workbook.getSheet(index);
		WritableCellFormat writableCellFormat = new WritableCellFormat();
		writableCellFormat.setBorder(Border.ALL, BorderLineStyle.DASHED,
				Colour.RED);
		Label labelCell = new Label(c, r, "");
		labelCell.setCellFormat(writableCellFormat);
		sheet.addCell(labelCell);
	}

	public void setCellBorderColor(int index, int col1, int row1, int col2, int row2)
			throws Exception {
		for (int i = col1; i < col2; i++) {
			for (int j = row1; j < row2; j++) {
				setCellBorderColor(index, i, j);
			}
		}
	}

	public void setColunmAutoSize(int index, int c) {
		WritableSheet sheet = workbook.getSheet(index);
		CellView cf = new CellView();
		sheet.setColumnView(c, cf);
	}

	public void mergeCells(int index, int col1, int row1, int col2, int row2)
			throws Exception {
		WritableSheet sheet = workbook.getSheet(index);
		sheet.mergeCells(col1, row1, col2, row2);
	}

	public void close() throws Exception {
		workbook.write();
		workbook.close();
	}

	public void createTable(int index, int r, int c) throws Exception {
		WritableSheet sheet = workbook.getSheet(index);
		WritableCellFormat writableCellFormat = new WritableCellFormat();
		writableCellFormat.setBorder(Border.ALL, BorderLineStyle.DASHED,
				Colour.RED);
		for (int i = 0; i < c; i++) {
			for (int j = 0; j < r; j++) {
				Label label = new Label(i, j, "");
				label.setCellFormat(writableCellFormat);
				sheet.addCell(label);
			}
		}
	}
}
